/*
 * Copyright (c) 2011. c.mueller
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * The Software shall be used for Good, not Evil.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package de.altitude.cimuelle.db;


import de.altitude.cimuelle.MessageBundle;
import de.altitude.cimuelle.coop.CoopGame;
import de.altitude.cimuelle.coop.CoopPlayer;
import de.altitude.cimuelle.coop.CoopPlayerArrayList;
import de.altitude.cimuelle.utils.ServerOperations;
import de.altitudecustomcommands.Player;
import org.apache.log4j.Logger;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * User: cybernaut
 * Date: 18.08.11
 * Time: 21:10
 */
public class DatabaseManager {

    private static final DatabaseManager dbManager = new DatabaseManager();

    private final java.sql.Connection conn = new DatabaseConnectionCreator().getCon();

    public static final String COOP_POINTS_COL = "coop_points";

    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPlayerToPlayerData = "INSERT INTO altitude_playerdata (vaporId, name, port) VALUES (?,?,?)";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToTurretKillByPlayer = "UPDATE altitude_playerdata SET turretKills = turretKills + ?, name = ? WHERE vaporId = ? AND port = ?";

    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToGoalsByPlayer = "UPDATE altitude_playerdata SET goals = goals + ?, name = ? WHERE vaporId = ? AND port = ?";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToAssistedGoalsByPlayer = "UPDATE altitude_playerdata SET assistedGoals = assistedGoals + ?, name = ? WHERE vaporId = ? AND port = ?";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToKillsByPlayer = "UPDATE altitude_playerdata SET kills = kills + ?, name = ? WHERE vaporId = ? AND port = ?";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToAssistedKillsByPlayer = "UPDATE altitude_playerdata SET assistedKills = assistedKills + ?, name = ? WHERE vaporId = ? AND port = ?";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlAddPointToDeathsByPlayer = "UPDATE altitude_playerdata SET deaths = deaths + ?, name = ? WHERE vaporId = ? AND port = ?";
    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlUpdateCoopPoints = "UPDATE altitude_playerdata SET " + COOP_POINTS_COL + " = " + COOP_POINTS_COL + " + ?, name = ? WHERE vaporId = ? AND port = ?";

    private final String sqlUpdatePlayerInfo = "UPDATE altitude_playerdata SET kills = ?, deaths = ?, goals = ?, " + COOP_POINTS_COL + " = ? WHERE vaporId = ? AND port = ?";

    private final String sqlSelectPlayerByName = "SELECT * FROM altitude_playerdata WHERE name = ? AND port = ?";
    private final String sqlSelectPlayerByVapor = "SELECT * FROM altitude_playerdata WHERE vaporId = ? AND port = ?";
    private final String sqlSelectPlayerOrder = "SELECT * FROM altitude_playerdata WHERE port = ? ORDER BY #ORDERBY# desc";

    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlSelectPlayerByRankOrder = "SELECT * FROM altitude_playerdata WHERE port = ? ORDER BY #ORDERBY# desc LIMIT ?";

    @SuppressWarnings({"FieldCanBeLocal"})
    private final String sqlSelectPlayerByRankFullOrder = "SELECT * FROM altitude_playerdata WHERE port = ? ORDER BY #ORDERBY# desc";


    //private final String sqlSelectDeathsByVapor = "SELECT count(*) as deaths FROM `altitude_killers` WHERE port=? AND v_vapor='?' AND assist=?;";

    //private final String sqlSelectGoalsPlayer = "SELECT s_vapor, s_name, COUNT(*) as goals FROM `altitude_goals` WHERE port=? AND assist=? GROUP BY s_vapor ORDER BY goals DESC";
    //private final String sqlSelectGoalsPlayerByVapor = "SELECT s_vapor, s_name, COUNT(*) as goals FROM `altitude_goals` WHERE port=? AND s_vapor=? AND assist=? GROUP BY s_vapor";
    private static final Logger LOGGER = Logger.getLogger("DatabaseManager");

    //private final String sqlSelectGoalsByVapor = "SELECT count(*) as goals FROM `altitude_goals` WHERE port=? AND s_vapor=?' AND assist=?;";
    //private final String sqlSelectKillsByVapor = "SELECT count(*) as kills FROM `altitude_killers` WHERE port=? AND s_vapor=? AND assist=?;";


    private DatabaseManager() {
    }

    public static synchronized DatabaseManager getInstance() {
        return dbManager;
    }

    private void addPlayerInDb(Player player) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sqlSelectPlayerByVapor);
            pst.setString(1, player.getVapor());
            pst.setString(2, player.getPort());
            LOGGER.debug(pst);
            rs = pst.executeQuery();
            //ResultSetMetaData rsm = rs.getMetaData();
            boolean isEmpty = true;
            if (rs.next()) {
                isEmpty = false;
            }
            synchronized (conn) {
                if (isEmpty) {
                    pst = conn.prepareStatement(sqlAddPlayerToPlayerData);
                    pst.setString(1, player.getVapor());
                    pst.setString(2, player.getName());
                    pst.setString(3, player.getPort());
                    LOGGER.debug(pst);
                    pst.executeUpdate();
                } else {
                }
            }
        } catch (SQLException e) {
            LOGGER.error(e, e);
        } catch (NullPointerException e) {
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
    }

    public List<DatabasePlayerInfo> getRankByName(String port, String name, String orderBy) {
        return getByName(port, name, sqlSelectPlayerOrder, orderBy);
    }

    private List<DatabasePlayerInfo> getByName(String port, String name, String sql, String orderBy) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql.replace("#ORDERBY#", orderBy));
            pst.setString(1, port);
            LOGGER.debug(pst);
            rs = pst.executeQuery();

            List<DatabasePlayerInfo> databasePlayerInfos = new ArrayList<DatabasePlayerInfo>();
            int counter = 0;
            while (rs.next()) {
                ++counter;
                //System.out.println(name.toLowerCase() + " .. " + rs.getString("name").toLowerCase());
                if (name.toLowerCase().equals(rs.getString("name").toLowerCase())) {
                    databasePlayerInfos.add(new DatabasePlayerInfo(counter, rs));
                    break;
                }
            }
            return databasePlayerInfos;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
        return null;
    }

    public List<DatabasePlayerInfo> getRankBy(String port, int amount, String orderBy) {
        return getRanks(port, amount, sqlSelectPlayerByRankOrder, orderBy);
    }

    private List<DatabasePlayerInfo> getRanks(String port, int amount, String sql, String orderBy) {
        try {
            PreparedStatement pst = conn.prepareStatement(sql.replace("#ORDERBY#", orderBy));
            pst.setString(1, port);
            pst.setInt(2, amount);
            LOGGER.debug(pst);
            ResultSet rs = pst.executeQuery();

            List<DatabasePlayerInfo> databasePlayerInfos = new ArrayList<DatabasePlayerInfo>();
            int counter = 1;
            while (rs.next()) {
                databasePlayerInfos.add(new DatabasePlayerInfo(counter, rs));
                ++counter;
                if (counter >= amount) {
                    break;
                }
            }
            rs.close();
            pst.close();
            return databasePlayerInfos;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        }
        return null;
    }


    public synchronized void addPointToPlayerForTurret(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToTurretKillByPlayer, 1);
        }
    }

    synchronized void addPointToPlayerForKills(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToKillsByPlayer, 1);
        }
    }

    synchronized void addPointToPlayerForAssistedKills(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToAssistedKillsByPlayer, 1);
        }
    }

    synchronized void addPointToPlayerForGoals(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToGoalsByPlayer, 1);
        }
    }

    synchronized void addPointToPlayerForAssistedGoals(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToAssistedGoalsByPlayer, 1);
        }
    }

    synchronized void addPointToPlayerForDeaths(Player[] players) {
        for (Player player : players) {
            addPointsToDatabase(player, sqlAddPointToDeathsByPlayer, 1);
        }
    }

    private synchronized void addPointsToDatabase(Player player, String sql, int value) {
        addPlayerInDb(player);
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, value);
            pst.setString(2, player.getName());
            pst.setString(3, player.getVapor());
            pst.setString(4, player.getPort());
            LOGGER.debug(pst);
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } catch (NullPointerException e) {
            LOGGER.error(e, e);
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    public List<DatabasePlayerInfo> getRankByVapor(Player player, String orderBy) {
        return getByVaporRank(player, sqlSelectPlayerOrder, orderBy);
    }

    private List<DatabasePlayerInfo> getByVaporRank(Player player, String sql, String orderBy) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql.replace("#ORDERBY#", orderBy));
            pst.setString(1, player.getPort());
            LOGGER.debug(pst);
            rs = pst.executeQuery();
            List<DatabasePlayerInfo> databasePlayerInfos = new ArrayList<DatabasePlayerInfo>();
            int counter = 0;
            LOGGER.debug(player.getPort() + ":looking for :'" + player.getVapor() + "'");
            while (rs.next()) {
                ++counter;
                LOGGER.debug(player.getPort() + ":looking for " + rs.getString("vaporId"));
                if (player.getVapor().equals(rs.getString("vaporId"))) {
                    databasePlayerInfos.add(new DatabasePlayerInfo(counter, rs));
                    break;
                }
            }
            return databasePlayerInfos;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
        return null;
    }

    public List<DatabasePlayerInfo> getMiddleRankBy(String port, int tcRank, Player player, String orderBy) {
        return getMiddleRank(port, tcRank, player, sqlSelectPlayerByRankFullOrder, orderBy);
    }

    private List<DatabasePlayerInfo> getMiddleRank(String port, int tcRank, Player player, String sql, String orderBy) {
        try {
            PreparedStatement pst = conn.prepareStatement(sql.replace("#ORDERBY#", orderBy));
            pst.setString(1, port);
            LOGGER.debug(pst);
            ResultSet rs = pst.executeQuery();

            List<DatabasePlayerInfo> tmpDatabasePlayerInfos = new ArrayList<DatabasePlayerInfo>();
            int counter = 1;
            int playerIndex = 1;
            while (rs.next()) {
                if (rs.getString("name").equals(player.getName())) {
                    playerIndex = counter;
                }
                tmpDatabasePlayerInfos.add(new DatabasePlayerInfo(counter, rs));
                ++counter;
            }
            List<DatabasePlayerInfo> databasePlayerInfos = new ArrayList<DatabasePlayerInfo>();
            int low = playerIndex - tcRank;
            low = (low < 1) ? 0 : low;
            int high = playerIndex + (tcRank - 1);
            high = (high > tmpDatabasePlayerInfos.size()) ? tmpDatabasePlayerInfos.size() - 1 : high;
            for (int i = low; i < high; i++) {
                databasePlayerInfos.add(tmpDatabasePlayerInfos.get(i));
            }
            rs.close();
            pst.close();
            return databasePlayerInfos;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        }
        return null;
    }

    public DatabasePlayerInfo getPlayerStats(String port, Player p) {
        int goals;
        int kills;
        int deaths;

        goals = getDataFromDatabaseByVapor(port, p, "goals");
        kills = getDataFromDatabaseByVapor(port, p, "kills");
        deaths = getDataFromDatabaseByVapor(port, p, "deaths");

        return new DatabasePlayerInfo(port, p, kills, deaths, goals);
    }

    private int getDataFromDatabaseByVapor(String port, Player p, String columnName) {
        return getDataFromDatabaseByVapor(port, p.getVapor(), columnName);
    }

    private int getDataFromDatabaseByVapor(String port, String vapor, String columnName) {
        int value = 0;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = conn.prepareStatement(sqlSelectPlayerByVapor);

            pst.setString(1, vapor);
            pst.setString(2, port);
            //pst.setString(3, assist);
            LOGGER.debug(pst);
            rs = pst.executeQuery();
            if (rs.next()) {
                value = rs.getInt(columnName);
            } else {
                LOGGER.error("Empty Dataset! " + vapor + " . " + port);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
        return value;
    }

    public Player getPlayerFromDatabase(String port, String vapor) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Player player = null;
        try {
            pst = conn.prepareStatement(sqlSelectPlayerByVapor);

            pst.setString(1, vapor);
            pst.setString(2, port);
            //pst.setString(3, assist);
            LOGGER.debug(pst);
            rs = pst.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                int turretKills = rs.getInt("turretKills");
                int goals = rs.getInt("goals");
                int assistedGoals = rs.getInt("assistedGoals");
                int kills = rs.getInt("kills");
                int assistedKills = rs.getInt("assistedKills");
                int deaths = rs.getInt("deaths");
                int coopPoints = rs.getInt("coop_points");
                //Timestamp lastupdated = rs.getTimestamp("lastUpdated");
                player = new Player(name, vapor, "", port, -1);
            } else {
                LOGGER.error("Empty Dataset! " + vapor + " . " + port);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
        return player;
    }

    private void closeConnections(PreparedStatement pst, ResultSet rs) {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
                LOGGER.debug(e);
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                LOGGER.debug(e);
            }
        }
    }

    public void addPointToPlayerForKills(Player player) {
        addPointToPlayerForKills(new Player[]{player});
    }

    public void addPointToPlayerForAssistedKills(Player player) {
        addPointToPlayerForAssistedKills(new Player[]{player});
    }

    public void addPointToPlayerForGoals(Player player) {
        addPointToPlayerForGoals(new Player[]{player});
    }

    public void addPointToPlayerForAssistedGoals(Player player) {
        addPointToPlayerForAssistedGoals(new Player[]{player});
    }

    public void addPointToPlayerForDeaths(Player victimP) {
        addPointToPlayerForDeaths(new Player[]{victimP});
    }

    public void increaseCoopPoints(String port, String vaporId, int points) {
        Player player = getPlayerFromDatabase(port, vaporId);
        increaseCoopPoints(player, points);
    }

    private void increaseCoopPoints(Player player, int points) {
        if (player != null) {
            addPointsToDatabase(player, sqlUpdateCoopPoints, points);
        } else {
            LOGGER.error("Cannot add '" + points + "' Points to Database for vapor ");
        }
    }

    void increaseCoopPoints(String port, CoopPlayer cplayer) {
        int points = cplayer.getCoopPoints();
        Player player = getPlayerFromDatabase(port, cplayer.getVapor());
        if (player == null) {
            LOGGER.debug(port + ":Creating new Player, none found in Database");
            player = new Player(cplayer.getName(), cplayer.getVapor(), "", port, -1);
        }
        int databaseCoopPoints = getCoopPointsByVapor(port, player.getVapor());
        if (databaseCoopPoints + points < 0) {
            points = databaseCoopPoints * -1;
        }
        increaseCoopPoints(player, points);
    }

    public void addCoopPlayerPoints(String port, CoopPlayerArrayList coopPlayers, CoopGame coopGame) {
        for (CoopPlayer player : coopPlayers) {
            increaseCoopPoints(port, player);

            HashMap<String, String> map = new HashMap<String, String>();
            map.put("ID", String.valueOf(coopGame.getDatabaseId()));
            map.put("ROUNDPOINTS", String.valueOf(player.getCoopPoints()));
            map.put("TOTALPOINTS", String.valueOf(DatabaseManager.getInstance().getCoopPointsByVapor(port, player.getVapor())));

            ServerOperations.sendServerWhisper(port, player.getName(), MessageBundle.getString("wcoopRoundEnd1", map));
            ServerOperations.sendServerWhisper(port, player.getName(), MessageBundle.getString("wcoopRoundEnd2", map));
        }
    }

    private int getCoopPointsByVapor(String port, String vapor) {
        return getDataFromDatabaseByVapor(port, vapor, COOP_POINTS_COL);
    }

    public ArrayList<DatabasePlayerInfo> getPlayerFromDatabaseByName(String port, String name) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<DatabasePlayerInfo> players = new ArrayList<DatabasePlayerInfo>();
        try {
            pst = conn.prepareStatement(sqlSelectPlayerByName);

            pst.setString(1, name);
            pst.setString(2, port);
            //pst.setString(3, assist);
            LOGGER.debug(pst);
            rs = pst.executeQuery();
            while (rs.next()) {
                String vapor = rs.getString("vaporId");
                int turretKills = rs.getInt("turretKills");
                int goals = rs.getInt("goals");
                int assistedGoals = rs.getInt("assistedGoals");
                int kills = rs.getInt("kills");
                int assistedKills = rs.getInt("assistedKills");
                int deaths = rs.getInt("deaths");
                int coopPoints = rs.getInt("coop_points");
                //Timestamp lastupdated = rs.getTimestamp("lastUpdated");
                players.add(new DatabasePlayerInfo(0, rs));
            }

            if (players.size() <= 0) {
                LOGGER.error(port + ":Empty Dataset! " + name + " .");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error(e, e);
        } finally {
            closeConnections(pst, rs);
        }
        return players;
    }

    public DatabasePlayerInfo getPlayerStats(String port, String vaporId) {
        Player player = getPlayerFromDatabase(port, vaporId);
        if (player != null) {
            return getPlayerStats(port, player);
        }
        return null;
    }

    public boolean updatePlayerInfo(DatabasePlayerInfo databasePlayerInfo) {
        boolean done = false;
        synchronized (conn) {
            PreparedStatement pst = null;
            try {
                pst = conn.prepareStatement(sqlUpdatePlayerInfo);
                pst.setInt(1, databasePlayerInfo.getKills());
                pst.setInt(2, databasePlayerInfo.getDeaths());
                pst.setInt(3, databasePlayerInfo.getGoals());
                pst.setInt(4, databasePlayerInfo.getCoopPoints());
                pst.setString(5, databasePlayerInfo.getVaporId());
                pst.setString(6, databasePlayerInfo.getPort());
                LOGGER.debug(pst);
                pst.executeUpdate();
                done = true;
            } catch (SQLException e) {
                e.printStackTrace();
                LOGGER.error(e, e);
            } catch (NullPointerException e) {
                LOGGER.error(e, e);
            } finally {
                if (pst != null) {
                    try {
                        pst.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return done;
    }
}
